package com.saic.parse.impl;

import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import com.saic.parse.IImportAmong;

public class ImportModel implements IImportAmong{

	public void importExcel(Connection conn,Connection connDataParsedb) throws Exception {

		Statement stmt = conn.createStatement();
		
		conn.createStatement().execute("DROP TABLE IF EXISTS t_model_b_x_a;");
		conn.createStatement().execute("CREATE TABLE `t_model_b_x_a` (`name` varchar(50) DEFAULT NULL,`bitauto` varchar(60) DEFAULT NULL,`xcar` varchar(50) DEFAULT NULL,`autohome` varchar(50) DEFAULT NULL, `id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
		stmt.execute("delete from t_velmodel");
		stmt.execute("delete from t_model_b_x_a");
		InputStream stream = new FileInputStream("d:/model.xls");  
		HSSFWorkbook wb = new HSSFWorkbook(stream); 
		Sheet sheet = wb.getSheetAt(0);  
		//获取数据
		for(Row row:sheet){
			
			//新的id  根据指定表的下一个增加量
			ResultSet resultSetNew = stmt.executeQuery("SELECT auto_increment FROM information_schema.`TABLES` WHERE TABLE_SCHEMA='bartack' AND TABLE_NAME='t_velmodel'");
			resultSetNew.next();
			int newId = resultSetNew.getInt(1);	//车系id新的id
			
			String a_brand_webid = null;		// 汽车之家主品牌webid 
			String a_brand_name = null;			// 汽车之家主品牌名
			String a_subbrand_webid = null;		// 汽车之家子品牌 webid
			String a_subbrand_name = null;		// 汽车之家子品牌名
			String a_desc = null;				// 描述
			String a_series_webid = null;		// 汽车之家车系webid
			String a_series_name = null;		// 汽车之家车系名
			String a_model_webid = null;		// 汽车之家车型webid
			String a_model_name = null;			// 汽车之家车型名
			String a_model_price = null;		// 汽车之家价格
			String a_model_year = null;
			int a_model_seriesid=0;
			
			
			String x_brand_webid = null;		// xcar主品牌webid
			String x_subbrand_webid = null;		// xcar子品牌webid
			String x_series_webid = null;		// xcar车系webid
			String x_series_name = null;		// xcar车系name
			String x_model_webid = null;		// xcar车型webid
			String x_model_name = null;			// xcar车型名
			String x_model_price = null;		// xcar价格
			
			String b_brand_webid = null;		// bitauto主品牌webid
			String b_subbrand_webid = null;		// bitauto子品牌webid
			String b_series_webid = null;		// bitauto车系webid
			String b_series_name = null;		// bitauto车系name
			String b_model_webid = null;		// bitauto车型webid
			String b_model_name = null;			// bitauto车型名
			String b_model_price = null;		// bitauto价格
			for(Cell cell:row){
				
				//主品牌web_id
				if(cell.getColumnIndex() == 0){
					a_brand_webid = cell.toString();
				}
				//主品牌name
				if(cell.getColumnIndex() == 1){
					a_brand_name = cell.toString();
				}
				//子品牌webid
				if(cell.getColumnIndex() == 2){
					a_subbrand_webid = cell.toString();
				}
				//子品牌name
				if(cell.getColumnIndex() == 3){
					a_subbrand_name = cell.toString();
				}
				//汽车之家车系webid
				if(cell.getColumnIndex() == 4){
					a_series_webid = cell.toString();
				}
				//汽车之家车系名
				if(cell.getColumnIndex() == 5){
					a_series_name = cell.toString();
				}
				//汽车之家车型webid
				if(cell.getColumnIndex() == 6){
					a_model_webid = cell.toString();
				}
				//汽车之家车型name
				if(cell.getColumnIndex() == 7){
					a_model_name = cell.toString();
				}
				//汽车之家车型price
				if(cell.getColumnIndex() == 8){
					a_model_price = cell.toString();
				}
				

				//爱卡车系web_id
				if(cell.getColumnIndex() == 23){
					x_series_webid = cell.toString();
				}
				//爱卡车系name
				if(cell.getColumnIndex() == 24){
					x_series_name = cell.toString();
				}
				//爱卡车型webid
				if(cell.getColumnIndex() == 25){
					x_model_webid = cell.toString();
				}
				//爱卡车型name
				if(cell.getColumnIndex() == 26){
					x_model_name = cell.toString();
				}
				//爱卡车型price
				if(cell.getColumnIndex() == 27){
					x_model_price = cell.toString();
				}
				
				
				//易车车系web_id
				if(cell.getColumnIndex() == 13){
					b_series_webid = cell.toString();
				}
				//易车车系name
				if(cell.getColumnIndex() == 14){
					b_series_name = cell.toString();
				}
				//易车车型webid
				if(cell.getColumnIndex() == 15){
					b_model_webid = cell.toString();
				}
				//易车车型name
				if(cell.getColumnIndex() == 16){
					b_model_name = cell.toString();
				}
				//易车车型price
				if(cell.getColumnIndex() == 17){
					b_model_price = cell.toString();
				}
				
			}
			//获取年款
			ResultSet resultYear = connDataParsedb.createStatement().executeQuery("select year_model from t_autohome_vel_model where web_id='"+a_model_webid+"'");
			while(resultYear.next()){
				a_model_year = resultYear.getString(1);
			}
			System.out.println(a_model_year);
			//获取车系id
			ResultSet resultSeries = conn.createStatement().executeQuery("select id from t_series_b_x_a where autohomeid='"+a_series_webid+"'");
			
			while(resultSeries.next()){
				a_model_seriesid = resultSeries.getInt(1);
			}
			PreparedStatement pstate = conn.prepareStatement("insert into t_velmodel (web_id,name,`desc`,guiding_price,velSeriesId,yearModel) values(?,?,?,?,?,?)");
			
			pstate.setString(1, a_model_webid);
			pstate.setString(2, a_model_name);
			pstate.setString(3, a_model_name);
			pstate.setString(4, a_model_price);
			pstate.setInt(5, a_model_seriesid);
			pstate.setString(6, a_model_year);

			pstate.execute();
			pstate.close();	
				
			PreparedStatement tempInsert = conn.prepareStatement("insert into t_model_b_x_a (name,bitauto,xcar,autohome,id) values(?,?,?,?,?)");
			tempInsert.setString(1,a_model_name);
			tempInsert.setString(2,b_model_webid);
			tempInsert.setString(3,x_model_webid);
			tempInsert.setString(4,a_model_webid);
			tempInsert.setInt(5,newId);
			tempInsert.execute();
			tempInsert.close();


		}			
	}

}
